use serde::Serialize;
use sqlx::Executor;
use sqlx::Row;
use sqlx::SqliteConnection;

use crate::error::new_err_hex_repe;
use crate::error::new_err_no_hex;
use crate::error::AppErr;
use crate::{error::SqlxErr, utils::current_timestamp};

use super::get_pool;

const CREATE_SQL: &'static str = r#"
CREATE TABLE IF NOT EXISTS tb_hex (
    id INTEGER PRIMARY KEY AUTOINCREMENT, 
    name TEXT NOT NULL, 
    pro_id INTEGER NOT NULL, 
    version TEXT NOT NULL, 
    description TEXT NOT NULL, 
    create_timestamp INTEGER NOT NULL, 
    size INTEGER NOT NULL,
    target TEXT NOT NULL, 
    body BLOB NOT NULL, 
    UNIQUE(pro_id, version)
)
"#;

pub async fn init() -> Result<(), SqlxErr> {
    get_pool().execute(CREATE_SQL).await?;
    Ok(())
}

async fn create_impl(
    conn: &mut SqliteConnection,
    name: &str,
    pro_id: i64,
    version: &str,
    description: &str,
    target: &str,
    body: &[u8],
) -> Result<i64, AppErr> {

    let ret = sqlx::query(r#"SELECT id FROM tb_hex WHERE pro_id = ? AND version = ?"#)
    .bind(pro_id)
    .bind(version)
    .fetch_optional(&mut *conn)
    .await?;
    if ret.is_some() {
        return Err(new_err_hex_repe());
    }

    let ret = sqlx::query(r#"
        INSERT INTO tb_hex (name, pro_id, version, description, create_timestamp, size, target, body) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
    "#)
    .bind(name)
    .bind(pro_id)
    .bind(version)
    .bind(description)
    .bind( current_timestamp() as i64 )
    .bind( body.len() as i64 )
    .bind(target)
    .bind(body)
    .execute( conn)
    .await?;

    Ok(ret.last_insert_rowid())
}

pub async fn create(
    name: &str,
    pro_id: i64,
    version: &str,
    description: &str,
    target: &str,
    body: &[u8],
) -> Result<i64, AppErr> {
    let mut tx = get_pool().begin().await?;
    let id = create_impl(&mut tx, name, pro_id, version, description, target, body).await?;
    tx.commit().await?;
    Ok(id)
}

#[derive(Debug, Serialize)]
pub struct Hex {
    id: i64,
    name: String,
    version: String,
    description: String,
    create_timestamp: i64,
    target: String,
    size: i64,
}

pub async fn query(pro_id: i64) -> Result<Box<[Hex]>, SqlxErr> {
    let rows = sqlx::query(
        r#"
        SELECT id, name, version, description, create_timestamp, target, size FROM tb_hex WHERE pro_id = ?
    "#,
    )
    .bind(pro_id)
    .fetch_all(get_pool())
    .await?;

    let mut vec = Vec::with_capacity(rows.len());
    for row in rows {
        let hex = Hex {
            id: row.get(0),
            name: row.get(1),
            version: row.get(2),
            description: row.get(3),
            create_timestamp: row.get(4),
            target: row.get(5),
            size: row.get(6),
        };
        vec.push(hex);
    }
    Ok(vec.into_boxed_slice())
}

pub async fn del(id: i64) -> Result<(), SqlxErr> {
    sqlx::query(r#"DELETE FROM tb_hex WHERE id = ?"#).bind(id).execute(get_pool()).await?;
    Ok(())
}

pub async fn get(id: i64) -> Result<(String, Box<[u8]>), AppErr> {
    let row = sqlx::query(r#"SELECT name, body FROM tb_hex WHERE id = ?"#).bind(id)
    .fetch_optional(get_pool())
    .await?
    .ok_or(new_err_no_hex())?;

    let name: String = row.get(0);
    let body: Box<[u8]> = row.get(1);

    Ok((name, body))
}